package com.hex.ds.hdtp.core.inf.common.utils;

import cn.hutool.core.util.StrUtil;
import cn.hutool.db.sql.SqlExecutor;
import com.hex.ds.hdtp.core.inf.common.exception.SourceJDBCException;
import com.hex.ds.hdtp.core.inf.common.exception.SourceJDBCExceptionDetail;
import com.hex.ds.hdtp.core.inf.metadata.po.SourceConfInfoPo;
import lombok.extern.slf4j.Slf4j;

import java.sql.*;
import java.util.*;
import java.util.stream.Collectors;

/**
 * @Package com.hex.ds.hdtp.core.inf.common.utils
 * @ClassName JdbcConnectUtil
 * @Description 通过Jdbc查询数据返回不同类型的值工具类
 * @Author Wang zhihao
 * @Date 2023/8/11 10:08
 * @Version v1.0
 **/
@Slf4j
public class JdbcConnectUtil {

    /**
     * @Method getOneFieldResult
     * @Param [sourceConfInfoPoList, querySql, field]
     * @Return java.util.List<java.lang.String>
     * @Description 返回单字段类型的查询结果
     * @Author Wang zhihao
     * @Date 2023/7/20 16:34
     * @Version V1.0
     */
    public static List<String> getOneFieldResult(List<SourceConfInfoPo> sourceConfInfoPoList, String querySql) throws SQLException {
        Connection jdbcConnection = null;
        Statement jdbcStatement = null;
        ResultSet resultSet = null;
        List<String> result = new ArrayList<>();
        try {
            jdbcConnection = JdbcUtil.getJdbcConnection(sourceConfInfoPoList);
            jdbcStatement = JdbcUtil.createJdbcStatement(jdbcConnection);
            resultSet = JdbcUtil.executeQuerySql(jdbcStatement, querySql);
            while (resultSet.next()) {
                String data = resultSet.getString(1).toUpperCase().trim();
                result.add(data);
            }
        } catch (Exception e) {
            throw new SQLException("getOneFieldResult JDBC连接查询异常:" + e.getMessage(), e);
        } finally {
            JdbcUtil.closeConnection(resultSet);
            JdbcUtil.closeConnection(jdbcConnection, jdbcStatement);
        }
        return result;
    }

    /**
     * @Method getLongResult
     * @Param [sourceConfInfoPoList, querySql]
     * @Return java.lang.Long
     * @Description 返回Long字段类型的查询结果
     * @Author Wang zhihao
     * @Date 2023/7/20 16:35
     * @Version V1.0
     */
    public static Long getLongResult(List<SourceConfInfoPo> sourceConfInfoPoList, String querySql) throws SQLException {
        Connection jdbcConnection = null;
        Statement jdbcStatement = null;
        ResultSet resultSet = null;
        long resultLong = 0L;
        try {
            jdbcConnection = JdbcUtil.getJdbcConnection(sourceConfInfoPoList);
            jdbcStatement = JdbcUtil.createJdbcStatement(jdbcConnection);
            resultSet = JdbcUtil.executeQuerySql(jdbcStatement, querySql);
            while (resultSet.next()) {
                resultLong = resultSet.getLong(1);
            }
        } catch (Exception e) {
            throw new SQLException("getLongResult JDBC连接查询异常:" + e.getMessage(), e);
        } finally {
            JdbcUtil.closeConnection(resultSet);
            JdbcUtil.closeConnection(jdbcConnection, jdbcStatement);
        }
        return resultLong;
    }

    /**
     * @Method getListResult
     * @Param [sourceConfInfoPoList, querySql]
     * @Return java.util.List<java.util.Map < java.lang.String, java.lang.Object>>
     * @Description 返回List类型的查询结果
     * @Author Wang zhihao
     * @Date 2023/7/20 16:35
     * @Version V1.0
     */
    public static List<Map<String, Object>> getListResult(List<SourceConfInfoPo> sourceConfInfoPoList, String querySql) throws SQLException {
        Connection jdbcConnection = null;
        Statement jdbcStatement = null;
        ResultSet resultSet = null;
        List<Map<String, Object>> rows = new ArrayList<Map<String, Object>>();
        try {
            jdbcConnection = JdbcUtil.getJdbcConnection(sourceConfInfoPoList);
            jdbcStatement = JdbcUtil.createJdbcStatement(jdbcConnection);
            resultSet = JdbcUtil.executeQuerySql(jdbcStatement, querySql);
            ResultSetMetaData rsm = resultSet.getMetaData();
            int count = rsm.getColumnCount();
            Map<String, Object> record = null;
            if (count > 0) {
                while (resultSet.next()) {
                    record = new HashMap<String, Object>();
                    for (int j = 0; j < count; j++) {
                        Object obj = resultSet.getObject(j + 1);
                        String columnName = rsm.getColumnName(j + 1);
                        record.put(columnName.toLowerCase(), (obj == null) ? "" : obj);
                    }
                    rows.add(record);
                }
            }
        } catch (Exception e) {
            throw new SQLException("getListResult JDBC连接查询异常:" + e.getMessage(), e);
        } finally {
            JdbcUtil.closeConnection(resultSet);
            JdbcUtil.closeConnection(jdbcConnection, jdbcStatement);
        }
        return rows;
    }

    /**
     * @Method getJdbcListResult
     * @Param [driver, url, user, psw, querySql]
     * @Return java.util.List<java.util.Map < java.lang.String, java.lang.Object>>
     * @Description 传入连接属性的查询-返回object
     * @Author Wang zhihao
     * @Date 2023/7/24 10:20
     * @Version V1.0
     */
    public static List<Map<String, Object>> getJdbcListResult(String driver, String url, String user, String psw, String querySql) throws SQLException {
        List<Map<String, Object>> rows = new ArrayList<Map<String, Object>>();
        try (Connection conn = DriverManager.getConnection(url, user, psw);
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(querySql)) {


            ResultSetMetaData rsm = rs.getMetaData();
            int count = rsm.getColumnCount();
            Map<String, Object> record = null;
            if (count > 0) {
                while (rs.next()) {
                    record = new HashMap<String, Object>();
                    for (int j = 0; j < count; j++) {
                        Object obj = rs.getObject(j + 1);
                        String columnName = rsm.getColumnName(j + 1).toUpperCase();
                        record.put(columnName.toLowerCase(), (obj == null) ? "" : obj);
                    }
                    rows.add(record);
                }
            }
        } catch (Exception e) {
            throw new SQLException("getJdbcListResult JDBC连接查询异常:" + e.getMessage(), e);
        }
        return rows;
    }

    /**
     * @param driver
     * @param url
     * @param user
     * @param psw
     * @param querySql
     * @Method getJdbcListStringResult
     * @Param
     * @Return java.util.List<java.util.Map < java.lang.String, java.lang.String>>
     * @Description 返回List<Map < String, String>>类型的查询结果
     * @Author Wang zhihao
     * @Date 2023/8/11 10:10
     * @Version V1.0
     */
    public static List<Map<String, String>> getJdbcListStringResult(String driver, String url, String user, String psw, String querySql) throws SQLException {
        List<Map<String, String>> rows = new ArrayList<Map<String, String>>();

        try (Connection conn = DriverManager.getConnection(url, user, psw);
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(querySql)) {


            ResultSetMetaData rsm = rs.getMetaData();
            int count = rsm.getColumnCount();
            Map<String, String> record = null;
            if (count > 0) {
                while (rs.next()) {
                    record = new HashMap<String, String>();
                    for (int j = 0; j < count; j++) {
                        String obj = rs.getString(j + 1).toLowerCase();
                        String columnName = rsm.getColumnName(j + 1);
                        record.put(columnName.toLowerCase(), (obj == null) ? "" : obj);
                    }
                    rows.add(record);
                }
            }
        } catch (Exception e) {
            throw new SQLException("getJdbcListStringResult JDBC连接查询异常:" + e.getMessage(), e);
        }
        return rows;
    }

    /**
     * @param sourceConfInfoPoList
     * @param querySql
     * @Method getStringResult
     * @Param
     * @Return java.sql.String
     * @Description 返回String类型的查询结果
     * @Author Wang zhihao
     * @Date 2023/8/11 10:14
     * @Version V1.0
     */
    public static String getStringResult(List<SourceConfInfoPo> sourceConfInfoPoList, String querySql) throws SQLException {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        String stringResult = null;
        try {
            conn = JdbcUtil.getJdbcConnection(sourceConfInfoPoList);
            stmt = JdbcUtil.createJdbcStatement(conn);
            rs = JdbcUtil.executeQuerySql(stmt, querySql);
            if (sourceConfInfoPoList.get(0).getSourceType().equals("MYSQL")) {
                while (rs.next()) {
                    stringResult = rs.getString(2);
                }
            } else if (sourceConfInfoPoList.get(0).getSourceType().equals("HIVE")) {
                StringBuilder stringBuilderResult = new StringBuilder();
                while (rs.next()) {
                    stringBuilderResult.append(rs.getString(1)).append("\n");
                }
                stringResult = String.valueOf(stringBuilderResult);
            } else {
                while (rs.next()) {
                    stringResult = rs.getString(1);
                }
            }
        } catch (Exception e) {
            throw new SQLException("getStringResult JDBC连接查询异常:" + e.getMessage(), e);
        } finally {
            JdbcUtil.closeConnection(rs);
            JdbcUtil.closeConnection(conn, stmt);
        }
        return stringResult;
    }

    /**
     * @param sourceConfInfoPoList
     * @param tableName
     * @Method getTableExist
     * @Param
     * @Return boolean
     * @Description 返回表是否存在的查询结果
     * @Author Wang zhihao
     * @Date 2023/8/17 16:19
     * @Version V1.0
     */
    public static boolean getTableExist(List<SourceConfInfoPo> sourceConfInfoPoList, String tableName) throws SQLException {
        Connection jdbcConnection = null;
        Statement jdbcStatement = null;
        ResultSet resultSet = null;
        boolean flag = false;
        try {
            jdbcConnection = JdbcUtil.getJdbcConnection(sourceConfInfoPoList);
            DatabaseMetaData metaData = jdbcConnection.getMetaData();
            String[] type = {"TABLE"};
            resultSet = metaData.getTables(null, null, tableName, type);
            flag = resultSet.next();
        } catch (Exception e) {
            throw new SQLException("getTableExist JDBC连接查询异常:" + e.getMessage(), e);
        } finally {
            JdbcUtil.closeConnection(resultSet);
            JdbcUtil.closeConnection(jdbcConnection);
        }
        return flag;
    }

    /**
     * @param sourceConfInfoPoList
     * @param querySql
     * @Method getFirstFieldResult
     * @Param
     * @Return List<String>
     * @Description 获取所有列名
     * @Author Wang zhihao
     * @Date 2023/8/22 10:46
     * @Version V1.0
     */
    public static List<String> getFieldResultList(List<SourceConfInfoPo> sourceConfInfoPoList, String querySql) throws SQLException {
        Connection jdbcConnection = null;
        Statement jdbcStatement = null;
        ResultSet resultSet = null;
        String columnName = "";
        List<String> columnNameList = new ArrayList<>();
        try {
            jdbcConnection = JdbcUtil.getJdbcConnection(sourceConfInfoPoList);
            jdbcStatement = JdbcUtil.createJdbcStatement(jdbcConnection);
            resultSet = JdbcUtil.executeQuerySql(jdbcStatement, querySql);
            ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
            int count = resultSetMetaData.getColumnCount();
            if (count > 0) {
                for (int j = 0; j < count; j++) {
                    columnName = resultSetMetaData.getColumnName(j + 1).toUpperCase();
                    columnNameList.add(columnName);
                }
            }
        } catch (Exception e) {
            throw new SQLException("getFieldResultList JDBC连接查询异常:" + e.getMessage(), e);
        } finally {
            JdbcUtil.closeConnection(resultSet);
            JdbcUtil.closeConnection(jdbcConnection, jdbcStatement);
        }
        return columnNameList;
    }

    /**
     * @Method runSql
     * @Param
     * @Return void
     * @Description 执行指定SQL
     * @Author Wang zhihao
     * @Date 2023/8/13 22:32
     * @Version V1.0
     */
    public static Boolean runSql(List<SourceConfInfoPo> sourceConfInfoPoList, String querySql) throws SQLException {
        Connection conn = null;
        Statement stmt = null;
        String currentSql = "";
        try {
            conn = JdbcUtil.getJdbcConnection(sourceConfInfoPoList);
            stmt = JdbcUtil.createJdbcStatement(conn);

            // 开发MySQL时发现，存在驱动不支持直接执行多条sql语句的情况，会将语句截断
            String[] sqls = querySql.split(";");
            for (String sql : sqls) {
                if (!sql.trim().isEmpty()) {
                    currentSql = sql.trim();
                    JdbcUtil.executeUpdatetSql(stmt, sql.trim());
                }
            }
        } catch (Exception e) {
            String errorMessage = StrUtil.format("执行SQL异常, 出错SQL: \n{} \n原因: \n{}", currentSql, e.getCause().getMessage());
            log.error("Jdbc连接执行SQL异常：{}", errorMessage, e);
            throw new SQLException("JDBC连接查询异常: " + errorMessage, e);
        } finally {
            JdbcUtil.closeConnection(conn, stmt);
        }
        return Boolean.TRUE;
    }

    /**
     * @Method runSqls
     * @Param sourceConfInfoPoList
     * @Param querySql
     * @Return com.hex.ds.hdtp.core.inf.common.exception.SourceJDBCException
     * @Description 执行指定SQL，返回执行结果
     * @Author Yin.Yang
     * @Date 2024/3/17 18:10
     * @Version V1.0
     */
    public static SourceJDBCException runSqls(List<SourceConfInfoPo> sourceConfInfoPoList, String querySql, boolean isRollback) {
        SourceJDBCException summary = new SourceJDBCException();
        ArrayList<SourceJDBCExceptionDetail> jdbcDetails = summary.getJdbcDetail();
        List<String> sqls = Arrays.stream(querySql.split(";")).map(String::trim)
                .filter(sql -> !sql.isEmpty()).collect(Collectors.toList());
        try (Connection conn = JdbcUtil.getJdbcConnection(sourceConfInfoPoList)) {
            if (isRollback) {
                conn.setAutoCommit(false);
            }
            Statement stmt = JdbcUtil.createJdbcStatement(conn);
            for (int index = 0; index < sqls.size(); index++) {
                String currentSql = sqls.get(index);
                try {
                    stmt.execute(currentSql);
                    jdbcDetails.add(new SourceJDBCExceptionDetail(index + 1, currentSql, true, ""));
                    log.info("执行SQL：{}", currentSql);
                    summary.incrementSuccessCount();
                } catch (Exception e) {
                    String errorMessage = e.getCause() != null ? e.getCause().getMessage() : e.getMessage();
                    jdbcDetails.add(new SourceJDBCExceptionDetail(index + 1, currentSql, false, errorMessage));
                    log.info("执行SQL异常, SQL: {}, 原因: {}", currentSql, errorMessage);
                    summary.incrementFailureCount();
                }
            }
            if (isRollback) {
                conn.rollback();
            }
        } catch (Exception e) {
            summary.setExecutionSuccess(false);
            log.info("Jdbc连接执行SQL异常", e);
        }
        summary.setTotalSqlCount(summary.getSuccessCount() + summary.getFailureCount())
                .setExecutionSuccess(summary.getFailureCount() == 0);
        return summary;
    }


    /**
     * @Method getTableExist
     * @Param
     * @Param sourceConfInfoPoList
     * @Param tableName
     * @Return boolean
     * @Description sql返回是否有结果
     * @Author xin.zhao
     * @Date 2023/10/16 19:16
     * @Version V1.0
     */
    public static boolean getSqlResult(List<SourceConfInfoPo> sourceConfInfoPoList, String querySql) throws SQLException {
        Connection jdbcConnection = null;
        Statement jdbcStatement = null;
        ResultSet resultSet = null;
        boolean flag = false;
        try {
            jdbcConnection = JdbcUtil.getJdbcConnection(sourceConfInfoPoList);
            jdbcStatement = JdbcUtil.createJdbcStatement(jdbcConnection);
            resultSet = JdbcUtil.executeQuerySql(jdbcStatement, querySql);
            flag = resultSet.next();
        } catch (Exception e) {
            throw new SQLException("getSqlResult JDBC连接查询异常:" + e.getMessage(), e);
        } finally {
            JdbcUtil.closeConnection(resultSet);
            JdbcUtil.closeConnection(jdbcConnection);
        }
        return flag;
    }

}
